package com.bst.etl.service.impl;

import com.bst.base.service.IBaseDimCompResultService;
import com.bst.base.service.IBaseDimCompService;
import com.bst.common.base.mapper.BaseMapper;
import com.bst.common.base.service.IBaseDWService;
import com.bst.common.constant.DwFieldConstants;
import com.bst.common.constant.JobConstant;
import com.bst.common.core.domain.AjaxResult;
import com.bst.common.exception.base.BaseRuntimeException;
import com.bst.common.utils.DateUtil;
import com.bst.common.utils.sql.EtlUtil;
import com.bst.common.vo.DimVO;
import com.bst.common.vo.HeaderAndBodyVO;
import com.bst.etl.domain.EtlDrawDm;
import com.bst.etl.service.IEtlDrawDmService;
import com.bst.etl.service.IEtlQryDmPubChartService;
import com.bst.etl.service.IEtlQryDmPubService;
import com.bst.etl.utils.IndexQueryUtil;
import com.bst.etl.vo.DmQueryIndexVO;
import com.bst.etl.vo.DmQueryParamVO;
import com.bst.etl.vo.QueryFieldVO;
import com.bst.etl.vo.chart.*;
import com.bst.md.domain.MdIndex;
import com.bst.md.domain.MdMedPubfld;
import com.bst.md.service.IMdIndexService;
import com.bst.md.service.IMdMedPubfldService;
import com.bst.md.service.IMdMedTbService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class EtlQryDmPubServiceImpl implements IEtlQryDmPubService {
    @Autowired
    IMdIndexService mdIndexService;
    @Autowired
    IEtlDrawDmService etlDrawDmService;
    @Autowired
    IMdMedTbService mdMedTbService;
    @Autowired
    IMdMedPubfldService mdMedPubfldService;
    @Autowired
    BaseMapper baseMapper;
    @Autowired
    IBaseDWService baseDWService;
    @Autowired
    IBaseDimCompResultService dimCompResultService;
    @Autowired
    IBaseDimCompService dimCompService;
    @Autowired
    IEtlQryDmPubChartService etlQryDmPubChartService;

    /**
     * @param paramVO
     * @return
     */

    @Override
    public List<Map<String,Object>> findSingleIndexData(DmQueryParamVO paramVO) {
        if(paramVO.getIdIndex()==null) {
            throw new RuntimeException("请添加查询指标");
        }
        List<Map<String,Object>> list = selectIndexData(paramVO);
        if(paramVO.getFgTb()) {
            tb(paramVO,list);
        }
        if(paramVO.getFgHb()) {
            hb(paramVO,list);
        }
        return list;
    }

    @Override
    public AjaxResult findIndexData(DmQueryParamVO paramVO) {
        List<DmQueryIndexVO> indexVOList = paramVO.getIndexVOS();
        for (DmQueryIndexVO vo : indexVOList) {
            paramVO.setIdIndex(vo.getIdIndex());
            List<Map<String,Object>> list = selectIndexData(paramVO);
            return AjaxResult.success(list);
        }
        return AjaxResult.success(null);
    }

    /**
     * 同比要求时间不能跨年
     * @param paramVO
     * @param dataList
     */
    private void tb(DmQueryParamVO paramVO,List<Map<String,Object>> dataList) {
        String oldStartDate = paramVO.getStartDate();
        String oldEndDate = paramVO.getEndDate();
        String startDate = paramVO.getStartDate();
        String endDate = paramVO.getEndDate();
        if(endDate!=null && !startDate.substring(0,4).equals(endDate.substring(0,4)) ) {
            return;
        }
        paramVO.setStartDate(thbDateReplace(startDate,"tb",paramVO.getDateType()));
        if(StringUtils.isNotEmpty(endDate)) {
            paramVO.setEndDate(thbDateReplace(endDate,"tb",paramVO.getDateType()));
        }
        List<Map<String,Object>> tbList = selectIndexData(paramVO);
        thb(dataList,tbList,paramVO.getIdIndex(),"tb", paramVO.getDateType());
        paramVO.setStartDate(oldStartDate);
        paramVO.setEndDate(oldEndDate);
    }

    /**
     * 环比要求结束时间为空，或者开始时间与结束时间一致
     * @param paramVO
     * @param dataList
     */
    private void hb(DmQueryParamVO paramVO,List<Map<String,Object>> dataList) {
        String oldStartDate = paramVO.getStartDate();
        String oldEndDate = paramVO.getEndDate();
        String startDate = paramVO.getStartDate();
        String endDate = paramVO.getEndDate();
        String dateType = paramVO.getDateType();
        if("Y".equals(dateType)) { // 年度查询不支持环比
            return;
        } else {
            Integer subLength = 0;
            if("D".equals(dateType)) {
                subLength = 10;
            } else if("M".equals(dateType)) {
                subLength = 7;
            } else if("Q".equals(dateType)) {
                subLength = 5;
            }
            if(endDate!=null && !startDate.substring(0,subLength).equals(endDate.substring(0,subLength)) ) {
                return;
            }
        }
        paramVO.setStartDate(thbDateReplace(startDate,"hb",paramVO.getDateType()));
        if(StringUtils.isNotEmpty(endDate)) {
            paramVO.setEndDate(thbDateReplace(endDate,"hb",paramVO.getDateType()));
        }
        List<Map<String,Object>> tbList = selectIndexData(paramVO);
        thb(dataList,tbList,paramVO.getIdIndex(),"hb", paramVO.getDateType());
        paramVO.setStartDate(oldStartDate);
        paramVO.setEndDate(oldEndDate);
    }

    private void thb(List<Map<String,Object>> dataList,List<Map<String,Object>> tbList,String idIndex,String type,String dateType) {
        String uniqueKey;
        Map<String,Map<String,Object>> v2Map = new HashMap<>();
        if(dataList==null || tbList==null) {
            return;
        }
        for (Map<String, Object> map : tbList) {
            uniqueKey = getUniqueKey(map);
            v2Map.put(uniqueKey,map);
        }
        Map<String,Object> v2Data;
        BigDecimal v1,v2;
        String day,day2,thbFld,thbRateFld;
        if("tb".equals(type)) {
            thbFld = DwFieldConstants.FIELD_YOY;
            thbRateFld = DwFieldConstants.FIELD_YOY_RATE;
        } else {
            thbFld = DwFieldConstants.FIELD_MOM;
            thbRateFld = DwFieldConstants.FIELD_MOM_RATE;
        }
        for (Map<String, Object> map : dataList) {
            uniqueKey = getUniqueKey(map);
            day = map.get(JobConstant.ID_DIM_DAY_OCCUR).toString();
            day2 = thbDateReplace(day,type,dateType);
            uniqueKey = uniqueKey.replace(day+",",day2+",");
            if((v2Data=v2Map.get(uniqueKey))!=null) {
                v1 = new BigDecimal(IndexQueryUtil.dataHandler(map.get(idIndex)).toString());
                v2 = new BigDecimal(IndexQueryUtil.dataHandler(v2Data.get(idIndex)).toString());
                map.put(thbFld,v2Data.get(idIndex));
                if(v2.compareTo(BigDecimal.ZERO)!=0) {
                    map.put(thbRateFld,IndexQueryUtil.resultHandler(v1.subtract(v2).divide(v2,4,BigDecimal.ROUND_HALF_UP),"percent"));
                }
            }
        }
    }

    /**
     * 同环比日期替换，比如2024-01-10,同比会替换成2023-01-10，环比会替换成2024-01-09
     * 不支持时间区间做同环比
     * 季度环比计算可能有点问题，用到了在调整
     */
    private String thbDateReplace(String day,String type,String dateType) {
        if("tb".equals(type)) {
            String year = day.substring(0,4);
            return (Integer.parseInt(year)-1)+day.substring(4);
        } else { //环比
            try {
                Integer calendarDateType=0;
                Date date = null;
                if(day.length()==7) {
                    date = DateUtil.FORMAT_YYYY_MM.parse(day);
                } else if(day.length()==10) {
                    date = DateUtil.FORMAT_YYYY_MM_DD.parse(day);
                }
                Date otherDate = null;
                switch (dateType) {
                    case "D":
                        otherDate = DateUtil.calculate(date,Calendar.DAY_OF_MONTH,-1);break;
                    case "M":
                        otherDate = DateUtil.calculate(date,Calendar.MONTH,-1);break;
                    case "Q":
                        otherDate = DateUtil.calculate(date,Calendar.MONTH,-3);break;
                }
                if(otherDate!=null) {
                    return DateUtil.FORMAT_YYYY_MM_DD.format(otherDate).substring(0,day.length());
                }
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private String getUniqueKey(Map<String,Object> data) {
        TreeMap<String,Object> dataMap = new TreeMap();
        dataMap.putAll(data);
        String values="",key;
        for(Map.Entry<String,Object> entry:dataMap.entrySet()) {
            key = entry.getKey();
            if(key.startsWith("IDX_")) {
                continue;
            }
            if(key.toUpperCase().startsWith("ID_") || key.toUpperCase().startsWith("DIM_")) {
                values += entry.getValue()+",";
            }
        }
        return values;
    }

    private List<Map<String,Object>> selectIndexData(DmQueryParamVO paramVO) {
        String idIndex = paramVO.getIdIndex();
        MdIndex mdIndex = mdIndexService.selectMdIndexByIdIndex(idIndex);
        EtlDrawDm dm = etlDrawDmService.findByIdIndexs(Arrays.asList(new String[]{idIndex})).get(idIndex);
        if(dm==null || dm.getFlds()==null || dm.getTbRelys()==null) {
            throw new BaseRuntimeException("指标提取规则未配置");
        }
        QueryFieldVO fieldVO = dimVOHandler(paramVO,Arrays.asList(dm.getFlds().split(",")));
        String querySql;
        //原子指标、子指标、可累加的复合指标，直接查询
        if(mdIndex.getEuTp()==0 || mdIndex.getEuTp()==2 || (mdIndex.getFgAccu()==1&&mdIndex.getEuTp()==1)) {
            querySql = getBaseIndexSql(dm,fieldVO,mdIndex,null);
        } else { // 不可累加的复合指标，需公式计算
            querySql = compIndexSql(dm,fieldVO);
        }
        List<Map<String,Object>> data = baseDWService.findBySql(querySql);
        return data;
    }

    @Override
    public AjaxResult findIndexDataWithHeader(DmQueryParamVO paramVO) {
        String idIndex = paramVO.getIndexVOS().get(0).getIdIndex();
        MdIndex mdIndex = mdIndexService.selectMdIndexByIdIndex(idIndex);
        EtlDrawDm dm = etlDrawDmService.findByIdIndexs(Arrays.asList(new String[]{idIndex})).get(idIndex);
        QueryFieldVO fieldVO = dimVOHandler(paramVO,Arrays.asList(dm.getFlds().split(",")));
        String querySql;
        //原子指标、子指标、可累加的复合指标，直接查询
        if(mdIndex.getEuTp()==0 || mdIndex.getEuTp()==2 || (mdIndex.getFgAccu()==1&&mdIndex.getEuTp()==1)) {
            querySql = getBaseIndexSql(dm,fieldVO,mdIndex,null);
        } else { // 不可累加的复合指标，需公式计算
            querySql = compIndexSql(dm,fieldVO);
        }
        List<Map<String,Object>> data = baseDWService.findBySql(querySql);
        HeaderAndBodyVO dataVO = new HeaderAndBodyVO();
        dataVO.setTbody(data);
        Map<String,String> headerMap = queryHeaders(data.size()>0?data.get(0):null);
        headerMap.put(idIndex,mdIndex.getNa());
        dataVO.setThead(headerMap);
        return AjaxResult.success(dataVO);
    }

    private String getBaseIndexSql(EtlDrawDm dm,QueryFieldVO fieldVO,MdIndex mdIndex,String valueDes) {
        String sqlWhere = dm.getSqlWhere();
        sqlWhere = StringUtils.isEmpty(sqlWhere)?"":" and "+sqlWhere;
        if(StringUtils.isEmpty(valueDes)) {
            String valueFld= EtlUtil.getIndexValueFld(mdIndex.getSdValtp());
            if(dm.getFgFromDw()==1) {
                valueDes = dm.getSqlValue()+" as "+dm.getIdIndex();
            } else {
                valueDes = "sum("+valueFld+") as "+dm.getIdIndex();
            }
        }
        String tableName = dm.getCdTb();
        if(dm.getFgFromDw()==1) {
            tableName = dm.getTbRelys();
        }
        String querySql = "select "+fieldVO.getSelectFlds()+valueDes+" from "+tableName+" dm "+
                (fieldVO.getJoins()==null?"":fieldVO.getJoins())+" where "+fieldVO.getWhereSql()+sqlWhere+" group by "+fieldVO.getGroupFlds();
        return querySql;
    }

    /**
     * 复合指标查询sql
     * @return
     */
    private String compIndexSql(EtlDrawDm dm,QueryFieldVO fieldVO) {
        List<String> relyIdIndexs = Arrays.asList(dm.getTbRelys().split(","));
        String formu = dm.getFormu();
        for(String id:relyIdIndexs) {
            formu = formu.replaceAll(id,"sum("+id+")");
        }
        String baseSql;
        StringBuffer sqlBuffer = new StringBuffer("select "+IndexQueryUtil.sqlAliasHandler(fieldVO.getSelectFlds())+","+formu+" as "+dm.getIdIndex()+" from (");
        Map<String, EtlDrawDm> dms = etlDrawDmService.findByIdIndexs(relyIdIndexs);
        List<MdIndex> indexs = mdIndexService.findByIds(relyIdIndexs,null,null);
        Map<String,MdIndex> indexMap = indexs.stream().collect(Collectors.toMap(MdIndex::getIdIndex, e->e));
        int i=0;
        for(String id:relyIdIndexs) {
            baseSql = getBaseIndexSql(dms.get(id),fieldVO,indexMap.get(id),getValueSql(dms.get(id),relyIdIndexs,indexMap.get(id),null,null));
            if(++i==relyIdIndexs.size()) {
                sqlBuffer.append(baseSql+") dm ");
            } else {
                sqlBuffer.append(baseSql+" union all ");
            }
        }
        sqlBuffer.append(" group by "+ IndexQueryUtil.sqlAliasHandler(fieldVO.getSelectFlds()));
        return sqlBuffer.toString();
    }

    private Map<String,String> queryHeaders(Map<String,Object> data) {
        Map<String,String> headerNameMap = new LinkedHashMap<>();
        if(data!=null) {
            List<String> idflds = new ArrayList<>();
            idflds.addAll(data.keySet());
            List<MdMedPubfld> pubflds = mdMedPubfldService.findByIdList(idflds);
            for (MdMedPubfld pubfld : pubflds) {
                headerNameMap.put(pubfld.getIdPubfld(),pubfld.getNa());
            }
        }
        return headerNameMap;
    }

    /**
     * totalFld 暂时不知道用处，先搁置
     * @param relyIdIndexs
     * @param alias 别名
     * @return
     */
    private String getValueSql(EtlDrawDm dm,List<String> relyIdIndexs,MdIndex index,String alias,String totalFld) {
        String valueSql = "";
        int i=0;
        if(index.getEuTp()==0 && index.getFgAccu()==1) { //可累加的原子指标
            totalFld = null;
        }
        if(StringUtils.isEmpty(alias)) {
            alias = index.getIdIndex();
        }
        if(relyIdIndexs==null || relyIdIndexs.size()==0) {
            if(StringUtils.isNotEmpty(totalFld)) {
                return totalFld+" as "+alias.toUpperCase();
            } else {
                return "sum(dm."+ IndexQueryUtil.getValueFld(index.getSdValtp())+") "+alias.toUpperCase();
            }
        }
        String idIndex = index.getIdIndex();
        for(String id:relyIdIndexs) {
            if(idIndex.equals(id)) {
                if(StringUtils.isNotEmpty(totalFld)) {
                    valueSql += totalFld+" as "+alias.toUpperCase();
                } else {
                    if(dm.getFgFromDw()==1) {
                        valueSql += dm.getSqlValue()+" as "+alias.toUpperCase();
                    } else {
                        valueSql += "sum(dm."+IndexQueryUtil.getValueFld(index.getSdValtp())+") "+alias.toUpperCase();
                    }
                }
            } else {
                valueSql += " 0 "+id.toUpperCase();
            }
            if(++i<relyIdIndexs.size()) {
                valueSql +=",";
            }
        }
        return valueSql;
    }

    @Override
    public Map<String, String> findFldNames(Map<String, Object> data, List<String> idIndexs) {
        return null;
    }

    @Override
    public Map<String, Object> querySingleDimIndex(String startDate, String type, List<DimVO> dimVOS, String[] idIndexses) {
        return null;
    }
    @Override
    public AjaxResult generateIndexWall(List<MdIndex> indexList, DmQueryParamVO paramVO) {
        ChartStyleVO styleVO = new ChartStyleVO();
        styleVO.setChartType("line");
        styleVO.setChartXVO(new ChartXVO("ID_DIM_DAY_OCCUR"));
        List<ChartYVO> chartYVOList = new ArrayList<>();
        List<IndexWallVO> wallVOS = new ArrayList<>();
        IndexWallVO wallVO;
        List<DmQueryIndexVO> indexVOS = new ArrayList<>();
        ChartParamVO shapeParamVO = new ChartParamVO();
        AjaxResult result;
        for (MdIndex index:indexList) {
            wallVO = new IndexWallVO();
            wallVO.setNaIndex(index.getNa());
            wallVO.setIdIndex(index.getIdIndex());
            wallVOS.add(wallVO);
            indexVOS.clear();
            chartYVOList.clear();
            indexVOS.add(new DmQueryIndexVO(index.getIdIndex()));
            paramVO.setIndexVOS(indexVOS);
            shapeParamVO.setDmQueryParamVO(paramVO);
            shapeParamVO.setChartStyleVO(styleVO);
            styleVO.setTitle(index.getNa());
            chartYVOList.add(new ChartYVO(index.getIdIndex(),index.getUnit()));
            styleVO.setChartYVOList(chartYVOList);
            result = this.findIndexDataWithHeader(shapeParamVO.getDmQueryParamVO());
            if(!AjaxResult.isSuccess(result)) {
                return result;
            }
            HeaderAndBodyVO headerAndBodyVO = (HeaderAndBodyVO)result.get("data");
            if(headerAndBodyVO!=null) {
                result = etlQryDmPubChartService.generateBaseShape(headerAndBodyVO,shapeParamVO.getChartStyleVO());
                if(AjaxResult.isSuccess(result)) {
                    Object data = ((ChartResultVO)result.get(AjaxResult.DATA_TAG)).getData();
                    wallVO.setShapeData(data==null?"":data.toString());
                }
            }
        }
        return AjaxResult.success(wallVOS);
    }

    private QueryFieldVO dimVOHandler(DmQueryParamVO paramVO,List<String> commonFlds) {
        List<DimVO> dimVOList = paramVO.getDimVOList();
        String dateType = paramVO.getDateType();
        QueryFieldVO fieldVO = new QueryFieldVO();
        String dayBegin = paramVO.getStartDate();
        String dayEnd = paramVO.getEndDate();
        String joins = "";
        String idPubfld;String groupFlds="",selectFlds="",otherFlds="",naFld;
        String whereSql = "",dimTable;
        //日期处理
        if("D".equals(dateType)) {
            groupFlds+= "dm.id_dim_day_occur,";
            selectFlds+= "dm.id_dim_day_occur as ID_DIM_DAY_OCCUR,";
            whereSql += " dm.id_dim_day_occur>='"+dayBegin+"' and dm.id_dim_day_occur<='"+dayEnd+"' ";
        } else if("M".equals(dateType)) {
            groupFlds+= "dy.mm,";
            selectFlds+= "dy.mm as ID_DIM_DAY_OCCUR,";
            joins +=("inner join md_dim_day dy on dy.id_day=dm.id_dim_day_occur ");
            if(StringUtils.isEmpty(dayEnd)) {
                whereSql += " dy.mm='"+dayBegin.substring(0,7)+"' ";
            } else {
                whereSql += " dy.mm>='"+dayBegin.substring(0,7)+"' and dy.mm<='"+dayEnd.substring(0,7)+"' ";
            }

        } else if("Q".equals(dateType)) {
            groupFlds+= "dy.qq,";
            selectFlds+= "dy.qq as ID_DIM_DAY_OCCUR,";
            joins +=("inner join md_dim_day dy on dy.id_day=dm.id_dim_day_occur ");
            whereSql += " dy.qq>='"+dayBegin.substring(0,5)+"' and dy.qq<='"+dayEnd.substring(0,5)+"' ";
        } else if("Y".equals(dateType)) {
            groupFlds+= "dy.yy,";
            selectFlds+= "dy.yy as ID_DIM_DAY_OCCUR,";
            joins +=("inner join md_dim_day dy on dy.id_day=dm.id_dim_day_occur ");
            whereSql += " dy.yy>='"+dayBegin.substring(0,4)+"' and dy.yy<='"+dayEnd.substring(0,4)+"' ";
        }
        if(dimVOList!=null && dimVOList.size()>0) {
            for(DimVO vo:dimVOList) {
                dimTable="";
                idPubfld = vo.getIdPubfld();
                groupFlds+= "dm."+idPubfld+",";
                selectFlds+= "dm."+idPubfld+" as "+idPubfld+",";
                if(idPubfld.toUpperCase().startsWith(JobConstant.ID_DIM_PREX)) {
                    naFld = idPubfld.replace(JobConstant.ID_DIM_PREX,JobConstant.NA_DIM_PREX);
                    if(commonFlds.contains(naFld)) {
                        groupFlds+= "dm."+naFld+",";
                        selectFlds+= "dm."+naFld+" as "+naFld+",";
                    }
                }
                //不包含日期
                if(StringUtils.isNotBlank(vo.getFilter())) {
                    if (vo.getFgJoin()) {
                        whereSql += " and "+dimTable+"."+vo.getFilter();
                        joins +=("left join "+dimTable+" on dm."+idPubfld+"="+dimTable+".cd ");
                    } else {
                        whereSql += " and "+vo.getFilter();
                    }
                }

            }
        }
        fieldVO.setGroupFlds(groupFlds==null?"":groupFlds.substring(0,groupFlds.length()-1));
        fieldVO.setSelectFlds(selectFlds==null?"":selectFlds);
        fieldVO.setWhereSql(whereSql==null?"":whereSql);
        fieldVO.setOtherFlds(otherFlds==null?"":otherFlds);
        fieldVO.setJoins(joins);
        return fieldVO;
    }
}
